""""
Case Type   : D库常用语法兼容-proc
Case Name   : D库procedure 支持 create or replace 创建存储过程，proc不支持
Create At   : 2025-11-19
Owner       : chen
Description :
    1.procedure创建存储过程
    2.调用存储过程
    3.proc创建存储过程
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro

log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class DDL_Proc_Case0005(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.constant = Constant()
        self.dbname_d = Node('PrimaryDbDmode').db_name
        self.proc1_name = 'p_proc1_case005'
        self.proc2_name = 'p_proc2_case005'

    def test_proc(self):
        text = '-----step1: procedure创建存储过程; expect: 成功-----'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_proc = f'''
            create or replace procedure {self.proc1_name}
            (para1 in integer,
                para2 inout integer)
            as
            begin
                raise info 'para1 is %', para1;
                raise info 'para2 is %', para2 * 3;
            end;'''
        res = primary_sh.execut_db_sql(create_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, res)

        text = '-----step2: 调用存储过程; expect: 成功-----'
        log.info(text)
        call_sql = f"call {self.proc1_name}(1, 2);"
        res = primary_sh.execut_db_sql(call_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn('INFO:  para1 is 1', res)
        self.assertIn('INFO:  para2 is 6', res)
        self.assertIn('2', res)

        text = '-----step3: proc创建存储过程; expect: 成功-----'
        log.info(text)
        create_proc = f'''
            create or replace proc {self.proc2_name}
            (para1 in integer,
                para2 inout integer)
            as
            begin
                raise info 'para1 is %', para1;
                raise info 'para2 is %', para2 * 2;
            end;'''
        res = primary_sh.execut_db_sql(create_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, res)
        call_sql = f"call {self.proc2_name}(1, 2);"
        res = primary_sh.execut_db_sql(call_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn('INFO:  para1 is 1', res)
        self.assertIn('INFO:  para2 is 4', res)
        self.assertIn('2', res)

    def tearDown(self):
        text = '-----step4: 清理环境; expect: 成功-----'
        log.info(text)
        drop_proc = f'drop proc if exists {self.proc1_name};' \
            f'drop proc if exists {self.proc2_name};'
        res = primary_sh.execut_db_sql(drop_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_PROCEDURE_SUCCESS_MSG, res)